package com.culture.bootdemo.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson.JSON;
import com.culture.bootdemo.model.dto.CommonDTO2;
import com.culture.bootdemo.model.vo.WriteData;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: wen
 * @DateTime: 2024/5/14 19:15
 * @Description:
 **/
public class JdbcConnDemo {
    public static void main(String[] args) {
        conn1();
    }

    public static void conn1() {
        // 数据库URL、用户名和密码
        String url = "jdbc:mysql://b2cpdb.idc3:3306/b2cadm";
        String username = "writeuser";
        String password = "ddbackend";

        try {
            // 加载并注册数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 建立数据库连接
            Connection connection = DriverManager.getConnection(url, username, password);
            // 创建Statement对象用于执行SQL语句
            Statement statement = connection.createStatement();
            // 执行SQL查询
            String sql = "SELECT shop_id,name FROM store  WHERE `status` = 1"; // 替换为你的表名
            ResultSet resultSet = statement.executeQuery(sql);
            List<CommonDTO2> list = new ArrayList<>();
            // 处理查询结果
            while (resultSet.next()) {
                // 假设表中有id和name两列，根据实际表结构调整
                Integer shopId = resultSet.getInt("shop_id");
                String name = resultSet.getString("name");
                list.add(new CommonDTO2(shopId, name));
            }
            System.out.println("list:" + list);
            conn2(list);
            // 记得关闭连接，避免资源泄露
            connection.close();
        } catch (ClassNotFoundException e) {
            System.err.println("找不到数据库驱动。");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("无法连接到数据库。");
            e.printStackTrace();
        }
    }


    /**
     * 有传参的
     * @param list
     */
    public static void conn2(List<CommonDTO2> list) {
        // 数据库URL、用户名和密码
        String url = "jdbc:mysql://10.5.12.171:3306/mdc_core";
        String username = "mdc_core_sir";
        String password = "r1s3r0ccdm";

        try {
            // 加载数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            List<CommonDTO2> result = new ArrayList<>();
            // 建立数据库连接
            try (Connection connection = DriverManager.getConnection(url, username, password)) {

                // 准备SQL语句，使用问号(?)作为参数占位符
                String sql = "SELECT param_value  FROM shop_param_value  WHERE id IN ( SELECT param_value_id FROM shop_param_map WHERE shop_id = ? )  AND type_id = 79";

                // 创建PreparedStatement对象
                try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {

                    for (CommonDTO2 dto : list) {
                        // 设置参数值，第一个参数是问号的位置索引（从1开始），第二个参数是要设置的值
                        Integer shopId = dto.getId();// 这是你想根据哪个值搜索的变量
                        preparedStatement.setInt(1, shopId);

                        // 执行查询
                        try (ResultSet resultSet = preparedStatement.executeQuery()) {
                            while (resultSet.next()) {
                                String param_value = resultSet.getString("param_value");
                                result.add(new CommonDTO2(dto.getId(), dto.getName(), param_value));
                            }
                        }
                    }
                }
            }

            System.out.println("result:" + JSON.toJSONString(result));
        } catch (ClassNotFoundException e) {
            System.err.println("找不到数据库驱动。");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("执行SQL时发生错误。");
            e.printStackTrace();
        }
    }

}
